#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class StdAttendanceProvider : CY.GFive.Core.Providers.DALProvider.IStdAttendanceProvider
	{
		#region Sql
		private static readonly string SqlSelectStdAttendance = "SELECT [Id], [ClassCode],[Times],[LeaveCate],[ShouldAttendance],[RealAttendance],[AtndRate],[Des],[YearNum],[EntryDate],[OperatorCode],[Week],[Term] FROM [StdAttendance] WHERE  [Id]=@Id";

		private static readonly string SqlInsertStdAttendance = "INSERT INTO [StdAttendance]([ClassCode],[Times],[LeaveCate],[ShouldAttendance],[RealAttendance],[AtndRate],[Des],[YearNum],[EntryDate],[OperatorCode],[Week],[Term]) values(@ClassCode,@Times,@LeaveCate,@ShouldAttendance,@RealAttendance,@AtndRate,@Des,@YearNum,@EntryDate,@OperatorCode,@Week,@Term);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateStdAttendance = "UPDATE [StdAttendance] SET [ClassCode] = @ClassCode,[Times] = @Times,[LeaveCate] = @LeaveCate,[ShouldAttendance] = @ShouldAttendance,[RealAttendance] = @RealAttendance,[AtndRate] = @AtndRate,[Des] = @Des,[YearNum] = @YearNum,[EntryDate] = @EntryDate,[OperatorCode] = @OperatorCode,[Week] = @Week,[Term] = @Term WHERE  [Id]=@Id";

		private static readonly string SqlDeleteStdAttendance = "DELETE [StdAttendance] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllStdAttendance = "SELECT [Id], [ClassCode],[Times],[LeaveCate],[ShouldAttendance],[RealAttendance],[AtndRate],[Des],[YearNum],[EntryDate],[OperatorCode],[Week],[Term] FROM [StdAttendance]";
		#endregion

		#region IStdAttendanceProvider Members
		public Core.Business.StdAttendance Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectStdAttendance);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.StdAttendance stdAttendance = new Core.Business.StdAttendance();

				if (!reader.IsDBNull(0)) stdAttendance.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) stdAttendance.ClassCode = reader.GetString(1);
				if (!reader.IsDBNull(2)) stdAttendance.Times = reader.GetInt32(2);
				if (!reader.IsDBNull(3)) stdAttendance.LeaveCate = reader.GetInt32(3);
				if (!reader.IsDBNull(4)) stdAttendance.ShouldAttendance = reader.GetInt32(4);
				if (!reader.IsDBNull(5)) stdAttendance.RealAttendance = reader.GetInt32(5);
				if (!reader.IsDBNull(6)) stdAttendance.AtndRate = reader.GetDecimal(6);
				if (!reader.IsDBNull(7)) stdAttendance.Des = reader.GetString(7);
				if (!reader.IsDBNull(8)) stdAttendance.YearNum = reader.GetString(8);
				if (!reader.IsDBNull(9)) stdAttendance.EntryDate = reader.GetDateTime(9);
				if (!reader.IsDBNull(10)) stdAttendance.OperatorCode = reader.GetString(10);
				if (!reader.IsDBNull(11)) stdAttendance.Week = reader.GetInt32(11);
				if (!reader.IsDBNull(12)) stdAttendance.Term = reader.GetInt32(12);

				return stdAttendance;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.StdAttendance stdAttendance)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@ClassCode", SqlDbType.NVarChar, stdAttendance.ClassCode);
			sql.AddParameter("@Times", SqlDbType.Int, stdAttendance.Times);
			sql.AddParameter("@LeaveCate", SqlDbType.Int, stdAttendance.LeaveCate);
			sql.AddParameter("@ShouldAttendance", SqlDbType.Int, stdAttendance.ShouldAttendance);
			sql.AddParameter("@RealAttendance", SqlDbType.Int, stdAttendance.RealAttendance);
			sql.AddParameter("@AtndRate", SqlDbType.Decimal, stdAttendance.AtndRate);
			sql.AddParameter("@Des", SqlDbType.NVarChar, stdAttendance.Des);
			sql.AddParameter("@YearNum", SqlDbType.NVarChar, stdAttendance.YearNum);
			sql.AddParameter("@EntryDate", SqlDbType.DateTime, stdAttendance.EntryDate);
			sql.AddParameter("@OperatorCode", SqlDbType.NVarChar, stdAttendance.OperatorCode);
			sql.AddParameter("@Week", SqlDbType.Int, stdAttendance.Week);
			sql.AddParameter("@Term", SqlDbType.Int, stdAttendance.Term);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertStdAttendance);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) stdAttendance.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.StdAttendance stdAttendance)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@ClassCode", SqlDbType.NVarChar, stdAttendance.ClassCode);
			sql.AddParameter("@Times", SqlDbType.Int, stdAttendance.Times);
			sql.AddParameter("@LeaveCate", SqlDbType.Int, stdAttendance.LeaveCate);
			sql.AddParameter("@ShouldAttendance", SqlDbType.Int, stdAttendance.ShouldAttendance);
			sql.AddParameter("@RealAttendance", SqlDbType.Int, stdAttendance.RealAttendance);
			sql.AddParameter("@AtndRate", SqlDbType.Decimal, stdAttendance.AtndRate);
			sql.AddParameter("@Des", SqlDbType.NVarChar, stdAttendance.Des);
			sql.AddParameter("@YearNum", SqlDbType.NVarChar, stdAttendance.YearNum);
			sql.AddParameter("@EntryDate", SqlDbType.DateTime, stdAttendance.EntryDate);
			sql.AddParameter("@OperatorCode", SqlDbType.NVarChar, stdAttendance.OperatorCode);
			sql.AddParameter("@Week", SqlDbType.Int, stdAttendance.Week);
			sql.AddParameter("@Term", SqlDbType.Int, stdAttendance.Term);
			sql.AddParameter("@Id", SqlDbType.Int, stdAttendance.Id);
			sql.ExecuteSql(SqlUpdateStdAttendance);
		}

		public void Delete(Core.Business.StdAttendance stdAttendance)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, stdAttendance.Id);
			sql.ExecuteSql(SqlDeleteStdAttendance);
		}

		public IList<Core.Business.StdAttendance> GetAllStdAttendance()
		{
			IList<Core.Business.StdAttendance> stdAttendancelist = new List<Core.Business.StdAttendance>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllStdAttendance);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.StdAttendance stdAttendance = new Core.Business.StdAttendance();

					if (!reader.IsDBNull(0)) stdAttendance.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) stdAttendance.ClassCode = reader.GetString(1);
					if (!reader.IsDBNull(2)) stdAttendance.Times = reader.GetInt32(2);
					if (!reader.IsDBNull(3)) stdAttendance.LeaveCate = reader.GetInt32(3);
					if (!reader.IsDBNull(4)) stdAttendance.ShouldAttendance = reader.GetInt32(4);
					if (!reader.IsDBNull(5)) stdAttendance.RealAttendance = reader.GetInt32(5);
					if (!reader.IsDBNull(6)) stdAttendance.AtndRate = reader.GetDecimal(6);
					if (!reader.IsDBNull(7)) stdAttendance.Des = reader.GetString(7);
					if (!reader.IsDBNull(8)) stdAttendance.YearNum = reader.GetString(8);
					if (!reader.IsDBNull(9)) stdAttendance.EntryDate = reader.GetDateTime(9);
					if (!reader.IsDBNull(10)) stdAttendance.OperatorCode = reader.GetString(10);
					if (!reader.IsDBNull(11)) stdAttendance.Week = reader.GetInt32(11);
					if (!reader.IsDBNull(12)) stdAttendance.Term = reader.GetInt32(12);

					stdAttendance.MarkOld();
					stdAttendancelist.Add(stdAttendance);
				}
				reader.Close();
			}
			return stdAttendancelist;
		}
		#endregion
	}
}
